********************************************************************************
*                                                                              *
*                          CREATE WAGE AND FIRM DATA                           *
*                                                                              *
********************************************************************************

// Directory
cd "H:\"

// Options
global beidmerge = 1

// Years to include
global yearlist 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020

////////////////////////////////////////////////////////////////////////////////
//////////////////////////////Create firm data//////////////////////////////////
////////////////////////////////////////////////////////////////////////////////

if $beidmerge == 1 {

	foreach year in $yearlist {

		use "Robots and Workers\Data\SPolis_`year'.dta", clear
		rename (hoursworked income_beftax companycar) (shoursworked sincome_gross scompanycar)
		drop income_gross // Use income before taxes, so ensure consistency with BAANSOMMEN data
		merge m:1 rinpersoon beid using "Robots and Workers\Data\Baansommen_`year'.dta", nogen
		rename (hoursworked income_gross companycar) (bhoursworked bincome_gross bcompanycar)
		
		foreach var in hoursworked income_gross companycar {		
			g `var' = s`var' if s`var' !=. & b`var' == .
			replace `var' = b`var' if s`var' ==. & b`var' != .
		}
		
		replace year = `year' if year == .
		
		// Yearly-weighted average of SPOLIS and BAANSOMMEN
		if `year' == 2010 {
			foreach var in hoursworked income_gross companycar {
				replace `var' = (s`var'*1 + b`var'*7)/8
			}
		}
		if `year' == 2011 {
			foreach var in hoursworked income_gross companycar {
				replace `var' = (s`var'*2 + b`var'*6)/8
			}
		}		
		if `year' == 2012 {
			foreach var in hoursworked income_gross companycar {
				replace `var' = (s`var'*3 + b`var'*5)/8
			}
		}
		if `year' == 2013 {
			foreach var in hoursworked income_gross companycar {
				replace `var' = (s`var'*4 + b`var'*4)/8
			}
		}
		if `year' == 2014 {
			foreach var in hoursworked income_gross companycar {
				replace `var' = (s`var'*5 + b`var'*3)/8
			}
		}
		if `year' == 2015 {
			foreach var in hoursworked income_gross companycar {
				replace `var' = (s`var'*6 + b`var'*2)/8
			}
		}
		if `year' == 2016 {
			foreach var in hoursworked income_gross companycar {
				replace `var' = (s`var'*7 + b`var'*1)/8
			}
		}
		drop bhoursworked bincome_gross bcompanycar shoursworked sincome_gross scompanycar
		
		g companycarXhoursworked = companycar*hoursworked
		g workers = 1
		
		collapse (sum) hoursworked income_gross  companycarXhoursworked workers, by(beid year)
		g companycar = companycarXhoursworked/hoursworked
		drop if beid == .
		save "Robots and Workers\Data\Beid_`year'_matched.dta", replace
	}

	clear all
	foreach year in $yearlist {
		append using "Robots and Workers\Data\Beid_`year'_matched.dta"
		erase "Robots and Workers\Data\Beid_`year'_matched.dta"
	}

	merge m:1 beid year using "Robots and Workers\Data\IHG_panel_20092020.dta", nogen keep(1 2 3) update 
	merge m:1 beid year using "Robots and Workers\Data\Investments.dta", nogen keep(1 2 3 4 5) update
	merge m:1 beid year using "Robots and Workers\Data\ABR.dta", nogen keep(1 2 3 4 5) update
	
	g loghoursworked = ln(hoursworked)
	order loghoursworked, after(hoursworked)
	g logincome_gross = ln(income_gross)
	order logincome_gross, after(income_gross)

	foreach var in imp_tot imp_mach imp_robots inv_total inv_computers inv_mach inv_softw inv_specsoftw {
		g logc`var' = ln(c`var')
		order logc`var', after(c`var')
		*drop `var'
	}

	compress
	save "H:\Data\BEID_merge.dta", replace	
	
	keep beid
	duplicates drop beid, force
	save "H:\Data\BEID_list.dta", replace	
	
	use "H:\Data\BEID_merge.dta", clear	
	merge m:1 beid year using "H:\Data\ProductionStatistics.dta", nogen keep(3)

	replace valueadded = . if valueadded <=1
	replace sales = . if sales <=1
	replace wagebill = . if wagebill <=1
	
	// Create robot adoption by competitors
	replace sbi = sbi08_5 if sbi == ""
	replace sbi = sbi_abr if sbi == ""
	g sbi4 = substr(sbi,1,4)
	destring sbi4, force replace
	drop if sbi4 == . & year >=2009
		
	g firmage = year-beidstyear+1
	g logfirmage = ln(firmage)
	g logvalueadded = ln(valueadded)
	g logsales = ln(sales)
	g logtotalcosts = ln(totalcosts)
	g logpersonnelcosts = ln(personnelcosts)
	g laborshare = wagebill/valueadded
	g laborshare_alt = wagebill/totalcosts
	replace laborshare = . if laborshare>1
	replace laborshare_alt = . if laborshare_alt>1	
	g loglaborshare = ln(laborshare)
	g loglaborshare_alt = ln(laborshare_alt)
	g logwagebill = ln(wagebill)
	g logworkers = ln(workers)
	g logempl = ln(empl)
	g salesphour = sales*1000/hoursworked
	replace salesphour = . if salesphour < 5 | salesphour > 10000
	g logsalesphour = ln(salesphour)
	g hwage = (wagebill*1000/hoursworked)
	replace hwage = . if hwage < 2.5 | hwage > 500
	g loghwage = ln(hwage)
	g valueaddedphour = valueadded*1000/hoursworked
	g logvalueaddedphour = ln(valueadded/hoursworked)
	g valueaddedpworker = valueadded*1000/workers
	g logvalueaddedpworker = ln(valueadded/workers)
	g capitalratio = capitalcosts/valueadded
	g logvalueaddedpworker09 = logvalueaddedpworker if year == 2009
	bysort beid: ereplace logvalueaddedpworker09 = mean(logvalueaddedpworker09)
	g hoursworked09 = hoursworked if year == 2009
	bysort beid: ereplace hoursworked09 = mean(hoursworked09)
	g logworkers09 = logworkers if year == 2009
	bysort beid: ereplace logworkers09 = mean(logworkers09)
	g logwagebill09 = logwagebill if year == 2009
	bysort beid: ereplace logwagebill09 = mean(logwagebill09)
	g logfirmage09 = logfirmage if year == 2009
	bysort beid: ereplace logfirmage09 = mean(logfirmage09)

	// 	Adjust event study variables
	foreach var in robots_t0 robots_t1 robots_t2 robots_t3 robots_t4 robots_gte2 robots_gte3 robots_gte4 robots_gte5 {
	   replace `var' = 0 if `var'==. & robotsfirm==0 
		if "`var'" == "robots_t0" {
			replace `var' = 0 if `var'==. & robotsfirm==1 & firmage<=1
		}	
		if "`var'" == "robots_t1" | "`var'" == "robots_gt1" {
			replace `var' = 0 if `var'==. & robotsfirm==1 & firmage<=2
		}
		if "`var'" == "robots_t2" | "`var'" == "robots_gt2" {
			replace `var' = 0 if `var'==. & robotsfirm==1 & firmage<=3
		}	
		if "`var'" == "robots_t3" | "`var'" == "robots_gt3" {
			replace `var' = 0 if `var'==. & robotsfirm==1 & firmage<=4
		}	
		if "`var'" == "robots_t4" | "`var'" == "robots_gt4" {
			replace `var' = 0 if `var'==. & robotsfirm==1 & firmage<=5
		}	
		if "`var'" == "robots_t5" | "`var'" == "robots_gt5" {
			replace `var' = 0 if `var'==. & robotsfirm==1 & firmage<=6
		}			
	}
	
	g robots_ltemin6 = 1-robots_tmin5-robots_tmin4-robots_tmin3-robots_tmin2-robots_tmin1-robots
	g robots_ltemin5 = 1-robots_tmin4-robots_tmin3-robots_tmin2-robots_tmin1-robots
	g robots_ltemin4 = 1-robots_tmin3-robots_tmin2-robots_tmin1-robots
	g robots_ltemin3 = 1-robots_tmin2-robots_tmin1-robots
	g robots_ltemin2 = 1-robots_tmin1-robots
	g robots_ltemin1 = 1-robots
	
	// Competition
	g robotsales=robots*sales
	bysort sbi4 year: egen totrobotsales=total(robotsales)
	bysort sbi4 year: egen totsales=total(sales)
	replace totrobotsales = totrobotsales-robotsales
	replace totsales = totsales-sales
	su totsales totrobotsales
	g compete=totrobotsales/totsales
	drop totrobotsales totsales
	g sbi3 = substr(sbi,1,3)
	destring sbi3, force replace
	
	// Drop if value added is missing
	drop if valueadded == . | laborshare ==. | hoursworked==.
	drop if workers > 25000
	order sbi sector *valueadded* *sales* *totalcosts *personnelcosts laborshare*, after(year)
					
	g manufacturing = sector == "industrie" | sector == "energie" | sector == "waterafval" | sector == "bouwnijverheid" | sector == "delfstoffenwinning" | sector == "transport"
	
	// Obtain regional identifiers
	merge m:1 mun year using "H:\Data\RegionIdentifiers.dta", nogen keep(1 3)
	order prov corop, after(mun)
	destring mun, force replace
	destring prov, force replace
	destring corop, force replace
	drop if mun == . & year >=2009
	
	foreach var in imp_tot cimp_tot imp_mach cimp_mach imp_robots cimp_robots exp_tot cexp_tot exp_mach cexp_mach exp_robots cexp_robots reexp_tot creexp_tot reexp_mach creexp_mach reexp_robots creexp_robots {
		replace `var' = 0 if `var' == . & year >=2009
	}
		
		
	do "H:\Do-files\Variable labels"
	save "H:\Data\BEID_PS_20002020.dta", replace
	keep if year >= 2009
	save "H:\Data\BEID_PS.dta", replace
	
}



	










